<?php
// import_csv.php - CSV文件导入到keyword表
// A列为keyword，B列为type，从第2行开始读取，每1000行批量插入

declare(strict_types=1);
ini_set('display_errors', '1');
error_reporting(E_ALL);

// 提升内存与执行时间配额
ini_set('memory_limit', '512M');
set_time_limit(0);

date_default_timezone_set('Asia/Shanghai');

// 读取数据库配置
$configPath = __DIR__ . '/config.php';
if (!file_exists($configPath)) {
    exit('<p style="font-family:Arial">缺少数据库配置文件 <code>config.php</code></p>');
}
$dbConfig = require $configPath;

// 创建PDO连接
$dsn = sprintf('mysql:host=%s;port=%d;dbname=%s;charset=%s',
    $dbConfig['db_host'],
    (int)$dbConfig['db_port'],
    $dbConfig['db_name'],
    $dbConfig['db_charset']
);

try {
    $pdo = new PDO($dsn, $dbConfig['db_user'], $dbConfig['db_pass'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$dbConfig['db_charset']}"
    ]);
} catch (Throwable $e) {
    exit('<p style="font-family:Arial">数据库连接失败: ' . htmlspecialchars($e->getMessage()) . '</p>');
}

$messages = [];
$stats = [
    'total_read' => 0,
    'total_inserted' => 0,
    'total_skipped' => 0,
    'batch_count' => 0,
    'time_cost' => 0
];

// 处理上传
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (!isset($_FILES['csv']) || $_FILES['csv']['error'] !== UPLOAD_ERR_OK) {
        $messages[] = ['type' => 'error', 'text' => '文件上传失败，请重试。'];
    } else {
        $tmpName = $_FILES['csv']['tmp_name'];
        $origName = $_FILES['csv']['name'] ?? 'upload.csv';
        $ext = strtolower(pathinfo($origName, PATHINFO_EXTENSION));
        
        if (!in_array($ext, ['csv'], true)) {
            $messages[] = ['type' => 'error', 'text' => '仅支持上传 .csv 文件。'];
        } else {
            try {
                $startTime = microtime(true);
                
                // 打开CSV文件
                $file = new SplFileObject($tmpName, 'r');
                $file->setFlags(SplFileObject::READ_CSV | SplFileObject::DROP_NEW_LINE | SplFileObject::SKIP_EMPTY);
                
                $lineNum = 0;
                $batch = [];
                $batchSize = 1000;
                $totalInserted = 0;
                $totalSkipped = 0;
                $batchCount = 0;
                
                // 准备批量插入的SQL语句
                $insertBatch = function(array $batch) use ($pdo, &$totalInserted, &$batchCount) {
                    if (count($batch) === 0) {
                        return;
                    }
                    
                    $pdo->beginTransaction();
                    try {
                        $stmt = $pdo->prepare('INSERT INTO `keyword` (`keyword`, `type`, `add_time`, `update_time`) VALUES (:keyword, :type, NOW(), NOW())');
                        
                        foreach ($batch as $row) {
                            $stmt->execute([
                                ':keyword' => $row['keyword'],
                                ':type' => $row['type']
                            ]);
                            $totalInserted++;
                        }
                        
                        $pdo->commit();
                        $batchCount++;
                    } catch (Throwable $e) {
                        $pdo->rollBack();
                        throw $e;
                    }
                };
                
                // 循环读取CSV文件
                foreach ($file as $row) {
                    if ($row === null || !is_array($row)) {
                        continue;
                    }
                    
                    $lineNum++;
                    
                    // 跳过第1行（标题行）
                    if ($lineNum === 1) {
                        continue;
                    }
                    
                    // 读取A列和B列
                    $keyword = isset($row[0]) ? trim((string)$row[0]) : '';
                    $type = isset($row[1]) ? trim((string)$row[1]) : '';
                    
                    // 跳过空行
                    if ($keyword === '' && $type === '') {
                        continue;
                    }
                    
                    // 如果keyword为空，跳过该行
                    if ($keyword === '') {
                        $totalSkipped++;
                        continue;
                    }
                    
                    // 如果type为空，使用默认值
                    if ($type === '') {
                        $type = 'default';
                    }
                    
                    // 添加到批次
                    $batch[] = [
                        'keyword' => $keyword,
                        'type' => $type
                    ];
                    
                    // 当批次达到1000行时，执行批量插入
                    if (count($batch) >= $batchSize) {
                        $insertBatch($batch);
                        $batch = []; // 清空批次
                    }
                }
                
                // 插入最后一批数据（不足1000行的部分）
                if (count($batch) > 0) {
                    $insertBatch($batch);
                }
                
                $endTime = microtime(true);
                $timeCost = round($endTime - $startTime, 2);
                
                // 统计信息
                $stats['total_read'] = $lineNum - 1; // 减去标题行
                $stats['total_inserted'] = $totalInserted;
                $stats['total_skipped'] = $totalSkipped;
                $stats['batch_count'] = $batchCount;
                $stats['time_cost'] = $timeCost;
                
                $messages[] = ['type' => 'success', 'text' => "导入成功！共读取 {$stats['total_read']} 行数据（不含标题行），成功插入 {$totalInserted} 条记录，跳过 {$totalSkipped} 条空数据，共执行 {$batchCount} 次批量插入，耗时 {$timeCost} 秒。"];
                
            } catch (Throwable $e) {
                $messages[] = ['type' => 'error', 'text' => '导入失败: ' . htmlspecialchars($e->getMessage())];
            }
        }
    }
}
?>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CSV导入到数据库 - keyword表</title>
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body { 
            font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", Arial, sans-serif;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            min-height: 100vh;
            padding: 40px 20px;
        }
        .container {
            max-width: 800px;
            margin: 0 auto;
        }
        .card {
            background: #fff;
            border-radius: 16px;
            box-shadow: 0 20px 60px rgba(0,0,0,0.15);
            overflow: hidden;
            animation: fadeIn 0.5s ease-in;
        }
        @keyframes fadeIn {
            from { opacity: 0; transform: translateY(20px); }
            to { opacity: 1; transform: translateY(0); }
        }
        .card-header {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: #fff;
            padding: 30px;
            text-align: center;
        }
        .card-header h1 {
            font-size: 28px;
            margin-bottom: 10px;
            font-weight: 600;
        }
        .card-header p {
            font-size: 14px;
            opacity: 0.95;
            line-height: 1.6;
        }
        .card-body {
            padding: 40px;
        }
        .form-group {
            margin-bottom: 24px;
        }
        label {
            display: block;
            margin-bottom: 10px;
            color: #333;
            font-weight: 600;
            font-size: 14px;
        }
        .file-input-wrapper {
            position: relative;
            display: inline-block;
            width: 100%;
        }
        input[type="file"] {
            width: 100%;
            padding: 12px;
            border: 2px dashed #ddd;
            border-radius: 8px;
            cursor: pointer;
            font-size: 14px;
            transition: all 0.3s ease;
        }
        input[type="file"]:hover {
            border-color: #667eea;
            background: #f8f9ff;
        }
        .tips {
            background: #f8f9ff;
            border-left: 4px solid #667eea;
            padding: 15px;
            margin-top: 15px;
            border-radius: 4px;
            font-size: 13px;
            color: #555;
            line-height: 1.8;
        }
        .tips strong {
            color: #667eea;
            display: block;
            margin-bottom: 8px;
        }
        .tips ul {
            margin-left: 20px;
        }
        .tips li {
            margin-bottom: 5px;
        }
        .btn {
            width: 100%;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: #fff;
            padding: 14px 28px;
            border: none;
            border-radius: 8px;
            cursor: pointer;
            font-size: 16px;
            font-weight: 600;
            transition: all 0.3s ease;
            box-shadow: 0 4px 15px rgba(102, 126, 234, 0.4);
        }
        .btn:hover {
            transform: translateY(-2px);
            box-shadow: 0 6px 20px rgba(102, 126, 234, 0.5);
        }
        .btn:active {
            transform: translateY(0);
        }
        .messages {
            margin-bottom: 24px;
        }
        .message {
            padding: 16px 20px;
            border-radius: 8px;
            margin-bottom: 12px;
            font-size: 14px;
            line-height: 1.6;
            display: flex;
            align-items: center;
            animation: slideIn 0.3s ease-out;
        }
        @keyframes slideIn {
            from { opacity: 0; transform: translateX(-20px); }
            to { opacity: 1; transform: translateX(0); }
        }
        .message::before {
            content: '';
            display: inline-block;
            width: 20px;
            height: 20px;
            margin-right: 12px;
            flex-shrink: 0;
        }
        .message.success {
            background: #d4edda;
            border-left: 4px solid #28a745;
            color: #155724;
        }
        .message.success::before {
            content: '✓';
            background: #28a745;
            color: #fff;
            border-radius: 50%;
            text-align: center;
            line-height: 20px;
            font-weight: bold;
        }
        .message.error {
            background: #f8d7da;
            border-left: 4px solid #dc3545;
            color: #721c24;
        }
        .message.error::before {
            content: '✕';
            background: #dc3545;
            color: #fff;
            border-radius: 50%;
            text-align: center;
            line-height: 20px;
            font-weight: bold;
        }
        .stats {
            background: #f8f9ff;
            border-radius: 8px;
            padding: 24px;
            margin-top: 24px;
        }
        .stats h3 {
            color: #333;
            margin-bottom: 16px;
            font-size: 18px;
        }
        .stats-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(150px, 1fr));
            gap: 16px;
        }
        .stat-item {
            background: #fff;
            padding: 16px;
            border-radius: 8px;
            text-align: center;
            box-shadow: 0 2px 8px rgba(0,0,0,0.05);
        }
        .stat-value {
            font-size: 28px;
            font-weight: 700;
            color: #667eea;
            margin-bottom: 4px;
        }
        .stat-label {
            font-size: 13px;
            color: #666;
        }
        code {
            background: #f4f4f4;
            padding: 2px 6px;
            border-radius: 4px;
            font-family: "Courier New", monospace;
            font-size: 13px;
            color: #e83e8c;
        }
    </style>
</head>
<body>
<div class="container">
    <div class="card">
        <div class="card-header">
            <h1>📊 CSV批量导入</h1>
            <p>将CSV文件中的数据批量导入到数据库 keyword 表</p>
        </div>
        <div class="card-body">
            <?php if (!empty($messages)): ?>
                <div class="messages">
                    <?php foreach ($messages as $msg): ?>
                        <div class="message <?php echo $msg['type']; ?>">
                            <?php echo $msg['text']; ?>
                        </div>
                    <?php endforeach; ?>
                </div>
            <?php endif; ?>

            <?php if ($stats['total_inserted'] > 0): ?>
                <div class="stats">
                    <h3>导入统计</h3>
                    <div class="stats-grid">
                        <div class="stat-item">
                            <div class="stat-value"><?php echo $stats['total_read']; ?></div>
                            <div class="stat-label">读取行数</div>
                        </div>
                        <div class="stat-item">
                            <div class="stat-value"><?php echo $stats['total_inserted']; ?></div>
                            <div class="stat-label">插入记录</div>
                        </div>
                        <div class="stat-item">
                            <div class="stat-value"><?php echo $stats['total_skipped']; ?></div>
                            <div class="stat-label">跳过记录</div>
                        </div>
                        <div class="stat-item">
                            <div class="stat-value"><?php echo $stats['batch_count']; ?></div>
                            <div class="stat-label">批次数量</div>
                        </div>
                        <div class="stat-item">
                            <div class="stat-value"><?php echo $stats['time_cost']; ?>s</div>
                            <div class="stat-label">执行时间</div>
                        </div>
                    </div>
                </div>
            <?php endif; ?>

            <form method="post" enctype="multipart/form-data">
                <div class="form-group">
                    <label for="csv">选择CSV文件</label>
                    <input type="file" name="csv" id="csv" accept=".csv" required>
                </div>

                <div class="tips">
                    <strong>📝 文件格式要求：</strong>
                    <ul>
                        <li>文件格式必须为 <code>.csv</code></li>
                        <li>第1行为标题行（将被跳过）</li>
                        <li>A列：<code>keyword</code>（搜索关键词）- 必填</li>
                        <li>B列：<code>type</code>（类型）- 可选，为空时使用默认值</li>
                        <li>从第2行开始读取数据到最后一行</li>
                        <li>每1000行批量插入一次，提高导入效率</li>
                    </ul>
                </div>

                <div class="form-group" style="margin-top: 30px;">
                    <button class="btn" type="submit">🚀 开始导入</button>
                </div>
            </form>
        </div>
    </div>
</div>
</body>
</html>

